package hiring.tong.com.common;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;






public class DBUtil {
	
	//获得Connection
	public static Connection getConnection(){
	Connection connection=null;
	   try {
		Class.forName(Constant.driver);
		connection=DriverManager.getConnection(Constant.url, Constant.user, Constant.password);
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return connection;	
}
//获取PreparedStatement;
	public static PreparedStatement getPreparedStatement(String sql){
		Connection connection=getConnection();
		PreparedStatement preparedStatement=null;
		try {
			preparedStatement=connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return preparedStatement;
	}
//关闭连接
	public static void close(Connection connection,PreparedStatement preparedStatement,ResultSet resultSet){
			try {
				if(connection!=null){
				connection.close();
			} 
				if(preparedStatement!=null){
					preparedStatement.close();
				}
				if(resultSet!=null){
					resultSet.close();
				}
				}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	}
//数据库增删该操作
	public static int executeUpdate(String sql,List<Object> paramList){
		PreparedStatement preparedStatement=null;
		try {
			 preparedStatement=getPreparedStatement(sql);
			setParam(preparedStatement,paramList);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("更新失败");
		}
		finally{
			close(null,preparedStatement,null);
		}
		
	}
	public static int executeUpdate(String sql,Object...objects){
		return executeUpdate(sql,Arrays.asList(objects));
	
	}
	//不带分页条件的查询操作
	public static <T> List<T> executeQuery(Class<T> clazz,String sql,List<Object> paramList){
		List<T> lists=new ArrayList<T>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try {
			connection=getConnection();
			preparedStatement=connection.prepareStatement(sql);
			setParam(preparedStatement,paramList);
			 resultSet=preparedStatement.executeQuery();
			 //读取数据库结构
			 ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
			//读取有多少列
			 int columnCount =resultSetMetaData.getColumnCount();
			 //给对应的类的属性赋值;
			while(resultSet.next()){
				T obj=clazz.newInstance();
				//换行每一行的列数
				for(int i=1;i<=columnCount;i++){
					//获取每一列的别名，没有取别名，则是字段名
				String columnLabel= resultSetMetaData.getColumnLabel(i);
				Field[] Fields=clazz.getDeclaredFields();
				//循环类的属性
				for(int j=0;j<Fields.length;j++){
					Fields[j].setAccessible(true);
					Class<?> type=Fields[j].getType();
					String name=Fields[j].getName();
					if(isBigLetter(name)){
						String changName=ChangField(name);
						if(changName.equals(columnLabel)){
							setFieldValue(resultSet, obj,columnLabel,Fields[j], type);
							break;
						}
						
					}
					else{
						if(name.equals(columnLabel)){
							setFieldValue(resultSet, obj,columnLabel,Fields[j], type);		
							break;
						}
						
					}
					
				}
		
				}
					
				lists.add(obj);
				}
			
			return lists; 
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		}
		finally{
			close(connection, preparedStatement, resultSet);
		}
		
		
	}
	public static <T> List<T> executeQuery(Class<T> clazz,String sql,Object...obj){
		return executeQuery( clazz, sql,Arrays.asList(obj));
	}
	public static <T> void setFieldValue(ResultSet resultSet,T obj,String columName,Field field,Class<?> fieldClass){

		try {
			if(fieldClass.equals(int.class)){
			field.set(obj, resultSet.getInt(columName));
			}
			else if(fieldClass.equals(String.class)){
				field.set(obj, resultSet.getString(columName));	
			}else if(fieldClass.equals(Date.class)){
				field.set(obj, resultSet.getTimestamp(columName));	
			}
		
		}catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
		
		
	
	//判断属性是否有大写
	public static boolean isBigLetter(String fieldName){
		char[] chars=fieldName.toCharArray();
		for(int i=0;i<chars.length;i++){
			if(chars[i]>=65&&chars[i]<=90){
				return true;
			}
		}
		return false;
	}
	//写属性的改为_格式
	public static String ChangField(String fieldName){
		if(isBigLetter(fieldName)){
			StringBuilder sb=new StringBuilder();
			char[] chars=fieldName.toCharArray();
			for(int i=0;i<chars.length;i++){
				if(chars[i]>=65&&chars[i]<=90){
					sb.append("_"+(char)(chars[i]+32));
				}
				else{
					sb.append(chars[i]);
				}
			}
			return sb.toString();
		}
		return fieldName;
	}
	
	
	
	
	
	
	//给preparedStatement语句赋值
	public  static void setParam(PreparedStatement preparedStatement,List<Object> paramList){
		for(int i=0;i<paramList.size();i++){
			try {
				preparedStatement.setObject(i+1, paramList.get(i));
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}	
	//返回单一对象
	public static <T> T getexecuteQuery(Class<T> clazz,String sql,List<Object> paramList){
		List<T> list=executeQuery(clazz,sql,paramList);
		if(list.isEmpty()){
			return null;
		} else{
		return list.get(0);
		}
	}
	public static <T> T getexecuteQuery(Class<T> clazz,String sql,Object...obj){
		return getexecuteQuery(clazz,sql,Arrays.asList(obj));
	}
	//截取指定字符串的位置
	//originalStrign为需要指定的字符串
	//designatedSting为指定的字符串
	public static int stingPlace(String originalStrign,String designatedSting){
		char[] original=originalStrign.toCharArray();
		char[] designated=designatedSting.toCharArray();
	
		for(int i=0;i<original.length;i++){
		
			if(designated[0]==original[i]){
				
				String newSting=originalStrign.substring(i, i+designated.length);
			
				if(newSting.equals(designatedSting)){
					return i;
				}
				
			}
		}
		return -1;
		
	}  
	//分页查询
	public static <T> PageModel<T> pageexecuteQuery(Class<T> clazz,String sql,int pageNo,int defaultPageSize,Object...obj){
		List<T> lists=executeQuery(clazz, sql, obj);
		return new PageModel<T>(lists,pageNo,defaultPageSize,getAllRecords(sql,Arrays.asList(obj)));
	}
	public static <T> PageModel<T> pageExecuteQuery(Class<T> clazz,String sql,int pageNo,int defaultPageSize,List<Object> paramList){
		List<T> lists=new ArrayList<T>();
		List<Integer> list=new ArrayList<Integer>();
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		int Number=(pageNo - 1) * defaultPageSize;
		try {
			connection=getConnection();
			preparedStatement=connection.prepareStatement(sql);
			setParam(preparedStatement,paramList);
			 resultSet=preparedStatement.executeQuery();
			 //读取数据库结构
			 ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
			//读取有多少列
			 int columnCount =resultSetMetaData.getColumnCount();
			 //给对应的类的属性赋值;
			while(resultSet.next()){
				list.add(++Number);
				T obj=clazz.newInstance();
				//换行每一行的列数
				for(int i=1;i<=columnCount;i++){
					//获取每一列的别名，没有取别名，则是字段名
				String columnLabel= resultSetMetaData.getColumnLabel(i);
				Field[] Fields=clazz.getDeclaredFields();
				//循环类的属性
				for(int j=0;j<Fields.length;j++){
					Fields[j].setAccessible(true);
					Class<?> type=Fields[j].getType();
					String name=Fields[j].getName();
					if(isBigLetter(name)){
						String changName=ChangField(name);
						if(changName.equals(columnLabel)){
							setFieldValue(resultSet, obj,columnLabel,Fields[j], type);
							break;
						}
						
					}
					else{
						if(name.equals(columnLabel)){
							setFieldValue(resultSet, obj,columnLabel,Fields[j], type);		
							break;
						}
						
					}
					
				}
		
				}
					
				lists.add(obj);
				}
			System.out.println(list);
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("数据查询失败");
		}
		finally{
			close(connection, preparedStatement, resultSet);
		}
		
		
		return new PageModel<T>(lists,pageNo,list,defaultPageSize,getAllRecords(sql,paramList));
	}
	public static <T> PageModel<T> pageexecuteQuery(Class<T> clazz,String sql,int pageNo,int defaultPageSize ,List<Object> paramList){
		List<T> lists=executeQuery(clazz, sql, paramList);
		return new PageModel<T>(lists,pageNo,defaultPageSize,getAllRecords(sql,paramList));
	}
	
	private static int getAllRecords(String sql,List<Object> paramList) {
		// TODO Auto-generated method stub
		int size=paramList.size();
		paramList.remove(size-1);
		paramList.remove(size-2);
		StringBuilder sb=new StringBuilder();
		sb.append("select count(*) ");
		String oldString=sql.substring(stingPlace(sql,"from"),stingPlace(sql,"limit"));
		sb.append(oldString);
		Connection connection=null;
		PreparedStatement preparedStatement=null;
		ResultSet resultSet=null;
		try{
			connection=getConnection();
			preparedStatement=connection.prepareStatement(sb.toString());
			setParam(preparedStatement,paramList);
		    resultSet=preparedStatement.executeQuery();
			resultSet.next();
			
			return resultSet.getInt(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("总记录查询失败");
		}
		finally{
			close(connection, preparedStatement, resultSet);
		}
		
	}
		
	}

